if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_getdeptdirector')
          and type in ('P','PC'))
   drop procedure bd_proc_getdeptdirector
go


create procedure bd_proc_getdeptdirector (@user_serial bigint) as
BEGIN
	DECLARE @user_dept_no varchar(50)--登录人所在部门部门dep_no
	DECLARE @find_user_serial bigint --遍历的人员id
	DECLARE @counts int --记录数
	DECLARE @find_user_dep int --遍历的人的部门ID
	DECLARE @find_user_dept_no varchar(50) -- 遍历的人的部门总监所在部门dep_no
	--明确哪一部分人归属登录人
--1.获取登录人所在部门及其下部门的人员

--2遍历人员本级向上级查找所有部门

--3部门关联部门负责人

--4获取部门dep_no最短的部门负责人记录

--5判断4步中的人员是否包含登录人，若包含，则保存当前遍历的人员


--最后将所有符合条件的人员返回，
	create table #find_users(
		user_serial bigint	
	)
	select @counts = count(1) from bd_kq_approve_group where group_id = 12 and user_serial = @user_serial;
	if @counts >0
	begin
		select @user_dept_no = d.dep_no from dt_user u inner join dt_dep d on u.user_dep = d.dep_serial
		where u.user_serial = @user_serial;
		
		DECLARE cursor_user CURSOR LOCAL FOR 
		SELECT 
		user_serial 
		from dt_user u
		inner JOIN dt_dep d on u.user_dep = d.dep_serial
		where d.dep_no like concat(@user_dept_no,'%')
		
		OPEN cursor_user FETCH NEXT FROM cursor_user INTO @find_user_serial--提取第一条数据存放在变量中    
				WHILE @@fetch_status = 0 
					BEGIN
					set @counts = 0;
					--如果该人员所在部门有部门总监，直接符合
					select @counts = count(1) from dt_user u inner JOIN dt_dep d on u.user_dep = d.dep_serial where d.dep_no = @user_dept_no and u.user_serial = @find_user_serial
					if @counts > 0
						BEGIN
							insert into #find_users VALUES(@find_user_serial);
						END
					else BEGIN
						select @find_user_dep = user_dep from dt_user where user_serial = @find_user_serial
							--递归获取上级部门,过滤掉不存在设置总监的部门，之后获取dep_no最短的部门，若该部门和登录人部门一致，则符合。
							;with temp 
								as
								(
								select  dep_serial,dep_no,dep_parent
								from dt_dep
								where dep_serial = @find_user_dep
								union all
								select d.dep_serial,d.dep_no,d.dep_parent
								from dt_dep d
								inner join temp t on t.dep_parent = d.dep_serial
								)
								select top 1 @find_user_dept_no = t.dep_no  from temp t inner join 
							(
								select u.user_dep from dt_user u inner join bd_kq_approve_group g  on u.user_serial = g.user_serial where group_id = 12 GROUP BY u.user_dep
							) x on x.user_dep  = t.dep_serial ;
							
							
							if @find_user_dept_no = @user_dept_no
								BEGIN
									insert into #find_users VALUES(@find_user_serial);
								END
						END
						FETCH NEXT FROM cursor_user INTO @find_user_serial
					END
		CLOSE cursor_user --关闭游标
		DEALLOCATE cursor_user --释放游标
	end
	select * from #find_users
	drop table #find_users
END
go
